package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;

import javax.xml.parsers.ParserConfigurationException;

import java.sql.ResultSetMetaData;

// under osx leopard java sdk: import com.mysql.jdbc.ResultSetMetaData;

import strings.strings;
import strings.deserializer;


/** manages connection to the database
 * idea is that other functions can simply "ask" the database questions and get answers out of this class.
 */
public class databaseInteraction {

	strings strings_inst = new strings();
	
	Connection connection = null; // one declare a local variable to hold our java.sql.Connection  object that one'll retrieve from java.sql.DriverManager later on.
	Statement statement = null;
	
	// <xml> stuff </xml>
	public StringBuffer answerXML = new StringBuffer();
	// /, /; separated list.
	public StringBuffer answerEXCEL = new StringBuffer();

	/** data easy to digest further via java
	 * is an ArrayList with all the data that the database returned
	 */
	public ArrayList<Object> answerJAVA = new ArrayList<Object>();

	// signal if the last query was successful
	public boolean success = true;
	
	// what is going on? last sql-error etc.
	// error or success messages that the caller might be interested in
	public String status = "";

	/** String excludeColumn -> pass "" except if there is a column that should not be returned (passwords of users) but columnName/key here **/
	String excludeColumn = "";
	/** if true -> a status="success" object/record will be included in every answer **/
	Boolean reportStatus = true;
	
	/** 
	 * function that handles the "asking" of the database.
	 * 
	 * NOTE!
	 * 
	 * it (per default) returns the Flex-XML stuff BUT!
	 * one can also get the "raw" java data via the answerJava property.
	 * 
	 * resultFormat -> in what format the result should be (default is xml) possible values:
	 * xml -> return data as flex digestable xml
	 * java -> no xml fency just the values, so that java can process them further.
	 * 
	 * INPUT:
	 * database_auth database_auth_inst -> a auth object containing mysql username/password
	 * String question -> question one wants to ask the database
	 * String excludeColumn -> pass "" except if there is a column that should not be returned (passwords of users) but columnName/key here
	 * String reportStatus -> if true -> "success" message should not be included in the output. (easier to process on client-flex side)
	 * **/
	public StringBuffer askDatabase(database_auth database_auth_inst, String question, String excludeColumn, Boolean reportStatus) 
	{
		this.excludeColumn = excludeColumn;
		this.reportStatus = reportStatus;
		return askDatabase(database_auth_inst, question); 
	}
	/** 
	 * function that handles the "asking" of the database.
	 * 
	 * NOTE!
	 * 
	 * it (per default) returns the Flex-XML stuff BUT!
	 * one can also get the "raw" java data via the answerJava property.
	 * 
	 * resultFormat -> in what format the result should be (default is xml) possible values:
	 * xml -> return data as flex digestable xml
	 * java -> no xml fency just the values, so that java can process them further.
	 * 
	 * INPUT:
	 * database_auth database_auth_inst -> a auth object containing mysql username/password
	 * String question -> question one wants to ask the database
	 * String excludeColumn -> pass "" except if there is a column that should not be returned (passwords of users) but columnName/key here
	 * **/
	public StringBuffer askDatabase(database_auth database_auth_inst, String question, String excludeColumn) 
	{
		this.excludeColumn = excludeColumn;
		return askDatabase(database_auth_inst, question); 
	}
	
	/** 
	 * function that handles the "asking" of the database.
	 * 
	 * NOTE!
	 * 
	 * it (per default) returns the Flex-XML stuff BUT!
	 * one can also get the "raw" java data via the answerJava property.
	 * 
	 * resultFormat -> in what format the result should be (default is xml) possible values:
	 * xml -> return data as flex digestable xml
	 * java -> no xml fency just the values, so that java can process them further.
	 * 
	 * INPUT:
	 * database_auth database_auth_inst -> a auth object containing mysql username/password
	 * String question -> question one wants to ask the database
	 * **/
	public StringBuffer askDatabase(database_auth database_auth_inst, String question) 
	{
		/** translate question from human english -> to database language **/
		answerXML = new StringBuffer();
		// /, /; separated list.
		answerEXCEL = new StringBuffer();

		answerJAVA = new ArrayList<Object>();
		
		if(database_auth_inst.host.equals(""))
		{
			database_auth_inst.host = "localhost";
		}

		String DatabaseName = database_auth_inst.DatabaseName;
		String tablename = database_auth_inst.TableName;
		String user = database_auth_inst.user;
		String password = database_auth_inst.password;
		String host = database_auth_inst.host;
		
		/* We encapsulate our JDBC code in a try/catch/finally block.
	     * This is to ensure that if any runtime error gets thrown we catch it and display it gracefully to the user.
	     */

	    try
	    {
	      Class.forName("com.mysql.jdbc.Driver").newInstance();
	      /* Now we take the first step in establishing a connection to our MySQL database by 
	       * loading the driver explicitly using Class.forName() method like this:
	       */
	      
	      connection = DriverManager.getConnection("jdbc:mysql://"+host+"/"+DatabaseName,user, password); // no password!?
	      /*
	       * Once the JDBC driver has been loaded in the JVM, we retrieve a connection to our MySQL database running
	       * on the local system using DriverManager.getConnection() method.
	       * The first argument to this method is complete database URL,
	       * next parameter is the user name and last parameter is the password.
	       * Note: You'd want to change the password described here with the actual password that you've setup for your MySQL 'root' account.
	       */
	      /** execute query & read data into answerXML **/
	      statement = connection.createStatement();

//	      PreparedStatement preparedstatement = connection.prepareStatement(question);
//	      preparedstatement.setString(1, spalte2Wert);
//	      ResultSet rset = preparedstatement.executeQuery();

	      ResultSet result = null;

	      // execute returns true if the next result is a ResultSet object; false if it is an update count or there are no more results 
	      if(statement.execute(question))
	      {
        	  result = statement.getResultSet();
        	  // try catch did not fail -> report success 
        	  if(reportStatus)
        	  {
        		  answerXML.append("<record>\n<status>success</status></record>\n");
        	  }
        	  status = "success";
        	  success = true;

        	  // retrieve data from dataBase
              java.sql.ResultSetMetaData resultMetaData = result.getMetaData();
              int numberOfColumns = resultMetaData.getColumnCount();

    	      while(result.next())
    	      {
    	    	  HashMap DATAElement = new HashMap();
    	    	  
            	  answerXML.append("<record>\n");
            	  for (int counter = 1; counter <= numberOfColumns; counter++)
            	  {
            		  String columnName = resultMetaData.getColumnName(counter);
            		  
            		  String value      = "";
            		  
            		  /** try-catch because of this problem: http://forums.sun.com/thread.jspa?threadID=786238
            		   * Error: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.
            		   * while retrieving an result from an mysql query� java returns an error that it can not convert the mysql-DATETIME value to TIMESTAMP� although one not asks for this� one wants just a simple string.
            		   * this is because '0000-00-00 00:00:00' is no valid value for a date in java. and somehow not even result.getString can cope with it.
            		   * actually there is no real workaround� except from this:
            		   **/
            		  try
            		  {
            			  value      = result.getString(counter);
            		  }
            		  catch(Exception e)
            		  {
            			  value      = "";
            			  System.err.println("Error: " + e.getMessage());
            		  }

            		  // System.out.println("key=" + columnName + " value=" + value + "");
            		  if(!columnName.equals(this.excludeColumn))
            		  {
            			  answerXML.append("<"+columnName+"><![CDATA["+value+"]]></"+columnName+">\n");
            			  DATAElement.put(columnName, value);
            		  }
            	  }
            	  
            	  answerJAVA.add(DATAElement);
            	  
            	  answerXML.append("</record>\n");

                  /** build answerEXCEL from DATAElement **/
                  Iterator iterator = DATAElement.keySet().iterator();
                  while( iterator.hasNext() )
                  {
               		  answerEXCEL.append(iterator.next()+"/,");
                  }

                  answerEXCEL = new StringBuffer(answerEXCEL.substring(0, answerEXCEL.length()-2));
                  answerEXCEL.append("/;");

                  iterator = DATAElement.values().iterator();
                  while( iterator.hasNext() )
                  {
                	  answerEXCEL.append(iterator.next()+"/,");
                  }

                  answerEXCEL = new StringBuffer(answerEXCEL.substring(0, answerEXCEL.length()-2));
                  answerEXCEL.append("/;");
    	      }
          }
	      else
	      {
        	  /**
        	   *  Retrieves the current result as an update count;
        	   *  if the result is a <code>ResultSet</code> object or there are no more results, -1
        	   *  is returned. This method should be called only once per result.
        	   * 
        	   * @return the current result as an update count; -1 if the current result is a
        	   * <code>ResultSet</code> object or there are no more results
        	   * @exception SQLException if a database access error occurs or 
        	   * this method is called on a closed <code>Statement</code>
        	   * @see #execute 
        	   */        	  
        	  int UpdateCount = statement.getUpdateCount();
        	  
        	  if(UpdateCount > 0)
        	  {
        		  answerXML.append("<record>\n<status>success</status></record>\n");
        		  answerEXCEL.append("success: "+question);
        		  status = "success: "+question;
        		  success = true;
        	  }
        	  else
        	  {
        		  // System.err.println("query successful");
        		  answerXML.append("<record>\n<status>"+"Success: Maybe there is no connection to database. executing query failed. </status></record>\n");
        		  answerEXCEL.append("Error: Maybe there is no connection to database, executing query failed;");
        		  status = "Error: Maybe there is no connection to database, executing query failed.";
        		  success = false;
        	  }
          }
          

//	      recordsUpdated = statement.executeUpdate("DELETE FROM customers WHERE user_id = 1");
//	      recordsUpdated = statement.executeUpdate("INSERT INTO `customers` (`customer_id`, `customer_name`, `customer_address`, `customer_type`, `entry_modified_date`) VALUES (1, 'Customer 1', 'Langford road, Bangalore', 'Company', '2009-08-04 15:30:44');");

	      // if(!connection.isClosed())System.out.println("Successfully connected to MySQL server using TCP/IP...");
	      /*
	       * If everything goes on well,
	       * above 2 statements are enough to successfully obtain a connection to our MySQL database.
	       * We then check to make sure we are properly connected and display a success message if we are.
	       */
	    }
	    catch(Exception e)
	    {
			System.err.println(e.getMessage());
			answerXML = new StringBuffer();
			answerXML.append("<record>\n<status>"+"Error: No connection to database. Is MySQL Service running? Or error in MySQL query.</status></record>\n");
			answerXML.append("<record>\n<error>"+e.getMessage()+"</error></record>\n");
			
			answerEXCEL.append("Error: query failed:"+e.getMessage());
			success = false;
			status = e.getMessage();
	    }
	    finally
	    {
	      try
	      {
	        if(connection != null)
	        {
	        	connection.close();
	        	/* Now to gracefully close the connection, we execute the Connection.close() 
	        	 * method in the finally block of our try/catch/finally statement.
	        	 * 
	        	 * Note: The Connection.close() method can also throw SQLException
	        	 * so that is why we encapsulate this close() method in a separate try/catch block.
	        	 */
	        }
	      }
	      catch(SQLException e)
	      {
		      System.err.println("Error: " + e.getMessage());
		      answerXML = new StringBuffer();
		      answerXML.append("<record>\n<status>"+"Error:SQLException: " + e.getMessage()+"</status></record>\n");
		      success = false;
		      status = e.getMessage();
	      }
	    }
		return answerXML;
	}

	/** checks if a given record in table in databse exists
	 * where record is in format:
	 * key1/=value1/,key2/=value2/,key3/=value3/;
	 * 
	 * UNTESTED!
	 * **/
	public Boolean exists(database_auth database_auth_inst, String TableName, String record)
	{
		Boolean result = true;
		String element_delimiter = "/;"; 
		String value_pair_delimiter = "/,"; 
		String key_value_delimiter = "/="; 
		deserializer deserializer_inst = new deserializer(element_delimiter, value_pair_delimiter, key_value_delimiter);
		HashMap LINES = deserializer_inst.deserialize(record);
		
    	Set<String> KEYSET = LINES.keySet();
    	Object[] KEYS = KEYSET.toArray();
    	
    	String WHERE = "";

    	/** iterate over lines **/
		for (int counter=0; counter<KEYS.length;counter++)
		{
			// keys & values
			HashMap<String,String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
			for ( String KEY : LINE.keySet() )
			{
				WHERE += "`"+KEY+"`='"+LINE.get(KEY)+"' AND ";
			}
		}
		
		WHERE = strings_inst.removeLastCharX(WHERE,5);
		
		String question = "SELECT * FROM `"+TableName+"` WHERE "+WHERE+";"; // mysql function to generate passsword hash: SELECT PASSWORD('djfkldjfkld!');
		
		database_auth_inst.TableName = TableName;
    	
    	askDatabase(database_auth_inst, question); // StringBuffer answer = DatabaseConnection.askDatabase( host, DatabaseName, TableName, user, password, question);

    	if(answerJAVA.size() != 0)
    	{
    		result = true;
    		
    		askDatabase( database_auth_inst, question);
    	}
    	else
    	{
    		result = false;
    	}
    	
    	return result;
	}
}